﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;

namespace LibFriesland_Campina_CRM
{
    class DalWareHousingDetail
    {
        //Them chi tiet phieu nhap kho
        public static int addWareHousingDetail(BizWareHousingDetail pWareHousingDetail)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                bool status = isExistWareHousingDetail(pWareHousingDetail.by_warehousing, pWareHousingDetail.by_product);
                if (status == false)
                {
                    WarehousingDetail wareHousingDetail = new WarehousingDetail();
                    wareHousingDetail.ByWarehousing = pWareHousingDetail.by_warehousing;
                    wareHousingDetail.ByProduct = pWareHousingDetail.by_product;
                    wareHousingDetail.Quantity = pWareHousingDetail.quantity;
                    db.WarehousingDetail.InsertOnSubmit(wareHousingDetail);
                    db.SubmitChanges();
                    return 1;
                }
                else
                    return updateWareHousingDetail(pWareHousingDetail); //Neu chi tiet phieu nhap kho da ton tai thi cap nhat them so luong
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        //Cap nhat chi tiet phieu nhap kho
        public static int updateWareHousingDetail(BizWareHousingDetail pWareHousingDetail)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var wareHousingDetail = db.WarehousingDetail.Single(p => p.ByWarehousing == pWareHousingDetail.by_warehousing && p.ByProduct == pWareHousingDetail.by_product);
                wareHousingDetail.Quantity += pWareHousingDetail.quantity;
                db.SubmitChanges();
                return 1;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        //Kiem tra chi tiet phieu nhap kho co ton tai
        public static bool isExistWareHousingDetail(string pBy_warehousing, string pBy_product)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            var wareHousingDetails = from item in db.WarehousingDetail
                                         where item.ByWarehousing == pBy_warehousing && item.ByProduct == pBy_product
                                         select item;
            if (wareHousingDetails.Count() == 0)
                return false;
            return true;
        }

        #region Dung cho Report
        //Thong ke so luong san pham nhap cua cua hang
        public static Dataset.DatasetWareHousing getWareHousingbyDateShop(DateTime pFromDate, DateTime pToDate)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            Dataset.DatasetWareHousing ds = new Dataset.DatasetWareHousing();
            DalBase dal = new DalBase();

            #region Tao du lieu cho bang Shop
            string commandText = "select shop_charid, name as shop_name from shop where shop_charid <> 'SHDEFAULT'";
            DataTable shopTable = dal.getData(commandText);
            foreach (DataRow item in shopTable.Rows)
            {
                ds.Tables["SHOP"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_CATALOGUE
            commandText = "select product_catalogue_charid, short_name as product_catalogue_name, by_product_brand from product_catalouge";
            DataTable productCatalogue = dal.getData(commandText);
            foreach (DataRow item in productCatalogue.Rows)
            {
                ds.Tables["PRODUCT_CATALOGUE"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BRAND
            commandText = "select product_brand_charid, name as product_brand_name from product_brand";
            DataTable productBrand = dal.getData(commandText);
            foreach (DataRow item in productBrand.Rows)
            {
                ds.Tables["PRODUCT_BRAND"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT
            commandText = " SELECT product_charid ,product.short_name as product_name, "
                        + " by_product_catalogue "
                        + " FROM product ";
            DataTable productTable = dal.getData(commandText);
            foreach (DataRow item in productTable.Rows)
            {
                ds.Tables["PRODUCT"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BY_SHOP
            string fromDateStr = pFromDate.ToString("yyyy-MM-dd");
            string toDateStr = pToDate.ToString("yyyy-MM-dd");
            commandText = " select product_in_shop.by_product, "
                + " case when temp.quantity is null then 0 else temp.quantity end as quantity, "
                + " product_in_shop.by_shop "
                + " from product_in_shop "
                + " left join "
                + " (SELECT by_product, sum(quantity) as quantity, by_shop FROM warehousing_detail "
                + " inner join warehousing on by_warehousing = warehousing_charid"
                + " where by_na <> 'natest' and DATE(datetime_phone_warehousing) >= '" + fromDateStr + "'"
                + " and DATE(datetime_phone_warehousing) <= '" + toDateStr + "'"
                + " group by by_product, by_shop) temp on temp.by_product = "
                + " product_in_shop.by_product and temp.by_shop = product_in_shop.by_shop ";

            DataTable productByShopTable = dal.getData(commandText);
            foreach (DataRow item in productByShopTable.Rows)
            {
                ds.Tables["PRODUCT_BY_SHOP"].ImportRow(item);
            }
            #endregion

            return ds;
        }
        #endregion
    }
}
